package com.mysql.DAO;
/*
* 模型层
* */
import com.mysql.Util.JDBCUtils;
import com.mysql.model.Students;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentsDAO {
//    增
    public void addStudents(Students stu) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into Students(name,age,phone) values(?,?,?)";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1,stu.getName());
        ptmt.setInt(2,stu.getAge());
        ptmt.setString(3,stu.getPhone());
        ptmt.execute();
        JDBCUtils.close(ptmt,conn);
    }

//    删
    public void delStudents(String name) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "delete from Students where name = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1,name);
        if(!ptmt.execute()){
            System.out.println("该学生信息删除成功！");
        }else{
            System.out.println("不存在该生，信息删除失败！");
        }
        JDBCUtils.close(ptmt,conn);
    }

//    查
//    得到一个学生信息
    public Students selStudents(String name) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "select * from Students where name = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1,name);
        ResultSet rs = ptmt.executeQuery();
        Students st = null;
        if (rs.next()) {
            st = new Students();
            st.setName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setPhone(rs.getString(4));
        } else {
            System.out.println("不存在该生！");
        }
        JDBCUtils.close(ptmt,conn);
        return st;
    }

//    得到所有学生信息
    public List<Students> getStudents() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select name,age,phone from Students");
        List<Students> stu = new ArrayList<Students>();
        Students st;
        while(rs.next()) {
            st = new Students();
            st.setName(rs.getString("name"));
            st.setAge(rs.getInt("age"));
            st.setPhone(rs.getString("phone"));
            stu.add(st);
        }
        JDBCUtils.close(rs,stmt,conn);
        return stu;
    }

//    改
    public void updateStudents(Students stu) throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "updata Students set name = ? , age = ? , phone = ? where id = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1,stu.getName());
        ptmt.setInt(2,stu.getAge());
        ptmt.setString(3,stu.getPhone());
        ptmt.setInt(4,stu.getId());
        ptmt.execute();
        JDBCUtils.close(ptmt,conn);
    }
}
